import datetime

import pymysql


def database_connect():
    return pymysql.connect(host='127.0.0.1', user='root', password='997968284', database='flaskdb',
                           cursorclass=pymysql.cursors.DictCursor)


def insert_to_user_register(user_id, user_name, pw_phone, pw_email, password):
    '''向注册表user_register中插入记录'''
    db = database_connect()  # 连接数据库
    cursor1 = db.cursor()  # 使用 cursor() 方法创建一个游标对象 cursor
    cursor2 = db.cursor()
    sql1 = "INSERT INTO user_register(user_id,user_name,pw_phone,pw_email,password)" \
           "VALUES('%s', '%s',  %s,  '%s',  %s)"  # 插入数值
    sql2 = "INSERT INTO %s (user_id,sign_up_date,telephone,email) VALUES('%s','%s','%s','%s')"
    tablename = ["organization_user_information", "personal_user_information", "admin_user_information"]
    try:
        cursor1.execute(sql1 % (user_id, user_name, pw_phone, pw_email, password))  # 执行sql语句
        cursor2.execute(sql2 % (tablename[int(user_id[0])], user_id, user_id[1:9], pw_phone, pw_email))
        db.commit()  # 提交
        flag = True
    except:
        db.rollback()  # 发生错误时回滚
        flag = False
    db.close()  # 关闭数据库连接
    return flag  # 返回数据写入状态，成功/失败


def insert_to_user_information(user_id, pw_phone, pw_email):
    '''user_information插入记录'''
    db = database_connect()  # 连接数据库
    cursor = db.cursor()  # 使用 cursor() 方法创建一个游标对象 cursor
    # 插入数值
    sql = "INSERT INTO %s (user_id,sign_up_date,telephone,email) VALUES('%s','%s','%s','%s')"
    tablename = ["organization_user_information", "personal_user_information", "admin_user_information"]
    try:
        cursor.execute(sql % (tablename[int(user_id[0])], user_id, user_id[1:9], pw_phone, pw_email))  # 执行sql语句
        db.commit()  # 提交
        flag = True
    except:
        db.rollback()  # 发生错误时回滚
        flag = False
    db.close()  # 关闭数据库连接
    return flag  # 返回数据写入状态，成功/失败


def insert_to_notice(notice_id,publisher_id, publish_date, content):
    '''notice插入公告记录'''
    db = database_connect()  # 连接数据库
    cursor = db.cursor()  # 使用 cursor() 方法创建一个游标对象 cursor
    # 插入数值
    sql = "INSERT INTO notice (notice_id,publisher_id,publish_date,content) VALUES('%s','%s','%s','%s')"

    try:
        cursor.execute(sql % (notice_id,publisher_id, publish_date, content))  # 执行sql语句
        db.commit()  # 提交
        flag = True
    except:
        db.rollback()  # 发生错误时回滚
        flag = False
    db.close()  # 关闭数据库连接
    return flag  # 返回数据写入状态，成功/失败


def query_by_username(user_name):
    '''从user_register中根据用户名查询用户注册信息'''
    db = database_connect()  # 连接数据库
    cursor = db.cursor()  # 使用 cursor() 方法创建一个游标对象 cursor
    # 插入数值
    sql = "select * from user_register where user_name = '%s' "
    try:
        cursor.execute(sql % (user_name))  # 执行sql语句
    except:
        print("Error: unable to fetch data")
    db.close()  # 关闭数据库连接
    result = cursor.fetchone()  # 获取查询结果,json格式
    if result is None:
        result = {}
    return result  # 返回查询结果


def query_user_information(user_name):
    '''根据用户名从user_information中查询用户的信息'''
    # query: user_name --> user_id
    user_id = query_by_username(user_name).get("user_id", None)
    if user_id is None:  # 未查到用户名
        return None
    db = database_connect()  # 连接数据库
    cursor = db.cursor()  # 使用 cursor() 方法创建一个游标对象 cursor
    sql = "select * from %s where user_id = '%s' "
    tablename = ["organization_user_information", "personal_user_information", "admin_user_information"]
    try:
        cursor.execute(sql % (tablename[int(user_id[0])], user_id))  # 执行sql语句
    except:
        print("Error: unable to fetch data")
    db.close()  # 关闭数据库连接
    result = cursor.fetchone()  # 获取查询结果，json格式
    return result  # 返回查询结果


def isExist(column_name, target_value):
    '''从user_register表中判断target_value在属性列column_name中是否已经存在'''
    db = database_connect()  # 连接数据库
    cursor = db.cursor()  # 使用 cursor() 方法创建一个游标对象 cursor
    sql = "select %s from user_register where %s = '%s' "  # 插入数值
    try:
        cursor.execute(sql % (column_name, column_name, target_value))  # 执行sql查询语句
    except:
        print("Error: unable to fetch data")
    db.close()  # 关闭数据库连接
    result = cursor.fetchone()  # 获取所有记录列表,结果类型为元组的元组，每一行为一个元组
    print(result)
    return result is not None


def delete_record_by_id(user_id, tables):
    '''通过user_id删除表中的一条记录'''
    db = database_connect()  # 连接数据库
    cursors = []
    for table_name in tables:
        cursors.append(db.cursor())  # 使用 cursor() 方法创建一个游标对象 cursor
    sql = "DELETE FROM %s WHERE user_id = %s "  # 删除数值

    try:
        for cursor, table_name in zip(cursors, tables):
            cursor.execute(sql % (table_name, user_id))  # 执行sql查询语句
        db.commit()
        flag = True
    except:
        print("Error: unable to delete record")
        db.rollback()
        flag = False
    db.close()  # 关闭数据库连接
    return flag


def deleteUserFromDB(user_name):
    '''删除用户信息'''
    user_id = query_by_username(user_name).get("user_id", None)
    if user_id is None:  # 未查到用户名
        return None

    tables = ['user_register']
    if user_id[0] == '0':
        tables.append('organization_user_information')
    elif user_id[0] == '1':
        tables.append('personal_user_information')
    elif user_id[0] == '2':
        tables.append('admin_user_information')
    return delete_record_by_id(user_id, tables)


def modifyInformation(table, main_attribute, main_attribute_value, attribute, attribute_value):
    '''根据主属性的值来修改表中该记录的某属性值'''
    db = database_connect()  # 连接数据库
    cursor = db.cursor()
    # SQL 更新语句
    sql = "UPDATE %s SET %s = '%s' WHERE %s = '%s' "
    try:
        cursor.execute(sql % (table, attribute, attribute_value, main_attribute, main_attribute_value))
        db.commit()
        flag = True
    except:
        db.rollback()
        flag = False
    db.close()
    return flag


# def updateInformation(table, main_attribute, main_attribute_value, attribute, attribute_value):
#     '''根据主属性的值来修改表中该记录的某属性值'''
#     db = database_connect()  # 连接数据库
#     cursor = db.cursor()
#     # SQL 更新语句
#     sql = "UPDATE %s SET %s = '%s' WHERE %s = %s "
#     try:
#         cursor.execute(sql % (table, attribute, attribute_value, main_attribute, main_attribute_value))
#         db.commit()
#         flag = True
#     except:
#         db.rollback()
#         flag = False
#     db.close()
#     return flag


def insert_to_post(table, post_id, publisher_id, theme, content, effective_date):
    '''seek_post或help_post表中插入记录'''
    db = database_connect()  # 连接数据库
    cursor = db.cursor()
    # SQL 插入语句
    now_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")  # 当前的时间
    sql = "INSERT INTO %s (post_id,publisher_id,publish_date,theme,content,effective_date,check_result,checker_id,number_of_views) VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s')"
    try:
        cursor.execute(sql % (table, post_id, publisher_id, now_time, theme, content, effective_date, 'N', '0' * 15, 0))
        db.commit()
        flag = True
    except:
        db.rollback()
        flag = False
    db.close()
    return flag


def delete_from_post(table, post_id):
    '''seek_post或help_post表中删除记录'''
    db = database_connect()  # 连接数据库
    cursor = db.cursor()
    # SQL 插入语句
    sql = "DELETE FROM %s WHERE post_id = '%s' "  # 删除数值
    try:
        cursor.execute(sql % (table, post_id))
        db.commit()
        flag = True
    except:
        db.rollback()
        flag = False
    db.close()
    return flag


def insert_to_mutual_help_info(mutual_help_id,helper_id,seek_id, post_id,title,content):
    '''mutual_help_info表中插入记录'''
    db = database_connect()  # 连接数据库
    cursor = db.cursor()
    # SQL 插入语句
    help_date = datetime.datetime.now().strftime("%Y-%m-%d")  # 当前的时间
    sql = "INSERT INTO mutual_help_info (mutual_help_id,helper_id,seek_id,help_date, post_id,number_of_views,title,content) VALUES('%s','%s','%s','%s','%s','%s','%s','%s')"
    try:
        cursor.execute(sql % (mutual_help_id,helper_id,seek_id,help_date, post_id,0,title,content))
        db.commit()
        flag = True
    except:
        db.rollback()
        flag = False
    db.close()
    return flag


class Database():
    def __init__(self, table, host, user, password, database, cursorclass):
        self.table = table
        self.db = pymysql.connect(host=host, user=user, password=password, database=database,
                                  cursorclass=cursorclass)

    def close(self):
        '''关闭数据库'''
        self.db.close()

    def query_all_records(self, attributes='*'):
        '''查询表table中的所有记录'''
        db = self.db  # 连接数据库
        cursor = db.cursor()  # 使用 cursor() 方法创建一个游标对象 cursor
        sql = "select %s from %s "
        try:
            cursor.execute(sql % (attributes, self.table))  # 执行sql语句
        except:
            print("Error: unable to fetch data")
        # db.close()  # 关闭数据库连接
        result = cursor.fetchall()  # 获取查询结果,json格式
        if result is None:
            result = {}
        return result  # 返回查询结果

    def query_a_records(self, attribute, attribute_value, attributes='*'):
        '''查询表table中的所有记录'''
        db = self.db  # 连接数据库
        cursor = db.cursor()  # 使用 cursor() 方法创建一个游标对象 cursor
        sql = "select %s from %s where %s = '%s' "
        try:
            cursor.execute(sql % (attributes, self.table, attribute, attribute_value))  # 执行sql语句
        except:
            print("Error: unable to fetch data")
        # db.close()  # 关闭数据库连接
        result = cursor.fetchone()  # 获取查询结果,json格式
        if result is None:
            result = {}
        return result  # 返回查询结果

    def updateData(self, main_attribute, main_attribute_value, attribute, attribute_value):
        '''根据主属性的值来更新表中该记录的某属性值'''
        db = self.db  # 连接数据库
        cursor = db.cursor()
        # SQL 更新语句
        sql = "UPDATE %s SET %s = '%s' WHERE %s = %s "
        try:
            cursor.execute(sql % (self.table, attribute, attribute_value, main_attribute, main_attribute_value))
            db.commit()
            flag = True
        except:
            db.rollback()
            flag = False
        return flag

# res = query_by_username("qweww")
# ress=unpacking(res)
# res = query_user_information("qweww")

# print(res)
# print(ress)
# https://www.runoob.com/python3/python3-mysql.html
